﻿/**
 * Created by Administrator on 2017/8/18 0018.
 */
var express = require('express');
var crypto = require("crypto");
var qr = require('qr-image')
var multer = require("multer");
var moment = require("moment");
var config = require('../../config.js');
var db = require('../../db/db.js');
const Excel = require("exceljs");
var http = require('http');
var fs = require('fs');
var path = require('path');
var https = require('https');
const compressing = require('compressing');
const request = require('request');
var xlsx = require("node-xlsx")


var router = express.Router();



var orders_model = require("../../model/orders");

var myutil = require("../../util/util");




router.post('/list', async function (req, res) {
    var page;
    var rows;
    if (isNaN(req.body.page)) {
        page = 1;
    } else {
        page = +req.body.page;
    }
    if (isNaN(req.body.rows)) {
        rows = 10;
    } else {
        rows = +req.body.rows;
    }
    var first = rows * (page - 1);


    var where = " 1=1  "
    if (typeof (req.body.mid) != "undefined" && req.body.mid != "" && req.body.mid != "0") {
        where = where + " and mid=" + req.body.mid;

    }

    if (req.body.pay_status != undefined && req.body.pay_status != "") {
        where += " and pay_status=" + req.body.pay_status;
    }
    if (req.body.ordernum != undefined && req.body.ordernum != "") {
        where += " and ordernum like '%" + req.body.ordernum + "%'";
    }

    if (req.body.stime != undefined && req.body.stime != "" && req.body.etime != undefined && req.body.etime != "") {
        where += " and createtime between '" + req.body.stime + "' and '" + req.body.etime + " 23:59:59' ";
    }


    console.log("where", where);



    var list = await orders_model.getlist(rows, first, where);
    for (var i = 0; i < list.length; i++) {


        list[i].createtime = moment(list[i].createtime).format("YYYY-MM-DD HH:mm:ss");
        if (list[i].pay_time != null) {
            list[i].pay_time = moment(list[i].pay_time).format("YYYY-MM-DD HH:mm:ss");
        }
        console.log("list[i].mid ", list[i].mid);
        if (list[i].mid == "0") {
            list[i].shopname = "自营";
        } else {
            var merchant = await merchant_model.getinfo("id=" + list[i].mid);
            if (merchant.length > 0) {
                list[i].shopname = merchant[0].name;
            } else {
                list[i].shopname = "自营";
            }
        }



    }
    console.log("list", list);
    var count = await orders_model.getcount(where);
    var allcount = parseInt(parseInt(count) / rows) + 1;
    if (count % rows == 0) {
        allcount = allcount - 1;
    }

    res.send({ status: 200, data: list, message: "", allcount: allcount, count: count });

});

router.delete('/del', async function (req, res) {

    var where = "id=" + req.body.id;
    var obj = await order_model.del(where);
    if (obj.code == 200) {
        var result = {
            "status": "200",
            "message": "success"
        }
        res.jsonp(result);
    } else {
        var result = {
            "status": "400",
            "message": "success"
        }
        res.jsonp(result);
    }


});

router.post('/info', async function (req, res) {

    var obj = await order_model.getinfo("id=" + req.body.id);
    if (obj.length > 0) {
        var result = {
            "status": "200",
            "message": "success",
            data: obj[0]
        }
        res.jsonp(result);
    } else {
        var result = {
            "status": "400",
            "message": "success"
        }
        res.jsonp(result);
    }


})



router.post("/fahuo", async function (req, res) {


    var obj = await db.execute("update yoshop_order set express_company=?,express_no=?,descs=?,order_status=? where order_no=?", [req.body.express_company, req.body.express_no, req.body.descs, 3, req.body.order_no])
    if (obj.code == 200) {
        //var goodorder = await db.execute("update yoshop_order_goods set order_status=3 where order_id=" + req.body.order_id);

        var result = {
            "status": "200",
            "message": "success"
        }
        res.jsonp(result);
    } else {
        var result = {
            "status": "400",
            "message": "error"
        }
        res.jsonp(result);
    }

})

router.post('/comment', async function (req, res) {

    var obj = await goodscomment_model.getinfo("order_id=" + req.body.id);
    if (obj.length > 0) {
        var result = {
            "status": "200",
            "message": "success",
            data: obj[0]
        }
        res.jsonp(result);
    } else {
        var result = {
            "status": "400",
            "message": "success"
        }
        res.jsonp(result);
    }


})

function getbase64(url) {

    return new Promise((resolve, reject) => {

        if (url.substring(0, 5) == "https") {
            https.get(url, function (res) {
                var chunks = []; //用于保存网络请求不断加载传输的缓冲数据
                var size = 0;　　 //保存缓冲数据的总长度
                res.on('data', function (chunk) {
                    chunks.push(chunk);　 //在进行网络请求时，会不断接收到数据(数据不是一次性获取到的)，
                    size += chunk.length;　　//累加缓冲数据的长度
                });
                res.on('end', function (err) {
                    var data = Buffer.concat(chunks, size);　　//Buffer.concat将chunks数组中的缓冲数据拼接起来，返回一个新的Buffer对象赋值给data
                    var base64Img = "data:image/png;base64," + data.toString('base64');　　//将Buffer对象转换为字符串并以base64编码格式显示
                    resolve(base64Img)
                });
            });

        } else {
            http.get(url, function (res) {
                var chunks = []; //用于保存网络请求不断加载传输的缓冲数据
                var size = 0;　　 //保存缓冲数据的总长度
                res.on('data', function (chunk) {
                    chunks.push(chunk);　 //在进行网络请求时，会不断接收到数据(数据不是一次性获取到的)，
                    size += chunk.length;　　//累加缓冲数据的长度
                });
                res.on('end', function (err) {
                    var data = Buffer.concat(chunks, size);　　//Buffer.concat将chunks数组中的缓冲数据拼接起来，返回一个新的Buffer对象赋值给data
                    var base64Img = "data:image/png;base64," + data.toString('base64');　　//将Buffer对象转换为字符串并以base64编码格式显示
                    resolve(base64Img)
                });
            });

        }
    });
}

function downloadFileAsync(uri, dest) {
    return new Promise((resolve, reject) => {
        // 确保dest路径存在
        var fileName = dest.substring(dest.lastIndexOf("/") + 1);
        const stream = fs.createWriteStream(dest);
        request(encodeURI(uri)).pipe(stream).on("close", function (err) {
            console.log("文件[" + fileName + "]下载完毕");

            resolve("success");
        });

    });
}



router.get("/exportExcel1", async function (req, res) {

    var where = " yoshop_order.order_status!=0  "

    //yoshop_order.pay_status=1 and yoshop_order.order_status=2

    if (req.query.order_no != undefined && req.query.order_no != "") {
        where += " and yoshop_order.order_no like '%" + req.query.order_no + "%'";
    }
    if (req.query.phone != undefined && req.query.phone != "") {
        where += " and yoshop_user.phone like '%" + req.query.phone + "%'";
    }
    if (req.query.pay_status != undefined && req.query.pay_status != "") {
        where += " and yoshop_order.pay_status=" + req.query.pay_status;
    }

    if (req.query.order_status != undefined && req.query.order_status != "") {
        where += " and yoshop_order.order_status=" + req.query.order_status;
    }


    if (req.query.stime != undefined && req.query.stime != "" && req.query.etime != undefined && req.query.etime != "") {
        where += " and yoshop_order.create_time between '" + req.query.stime + "' and '" + req.query.etime + " 23:59:59' ";
    }


    const workbook = new Excel.Workbook();
    workbook.created = new Date();
    workbook.modified = new Date();
    let sheet = workbook.addWorksheet("excel文件名称", {
        properties: { defaultRowHeight: 80 },
    });

    sheet.columns = [

        { header: "订单编号", key: "ordernum" },
        { header: "商品名称", key: "name" },
        { header: "日文名称", key: "japanname" },
        { header: "商品编码", key: "code" },
        { header: "购买数量", key: "num" },
        { header: "价格", key: "price" },
        { header: "商品规格", key: "goods_attr" },
        { header: "商品重量", key: "goods_weight" },
        { header: "备注", key: "descs" },
        { header: "订单状态", key: "order_status" },
        { header: "用户名", key: "phone" },
        { header: "身份证号", key: "idcard" },


    ];
    res.header("Access-Control-Allow-Origin", "*");

    var fileName = "order";

    console.log("where11111111111", where);
    var orderlist = await order_model.getinfo(where, " join yoshop_user on yoshop_user.user_id= yoshop_order.user_id ", " order by yoshop_order.id desc ", "yoshop_order.*,yoshop_user.nickname,yoshop_user.phone");

    var arr = [];
    for (var i = 0; i < orderlist.length; i++) {
        var goodlist = await order_goods_model.getinfo("order_id=" + orderlist[i].id
            , " join yoshop_goods_spec on yoshop_goods_spec.id= yoshop_order_goods.goods_spec_id",
            " order by yoshop_order_goods.id asc ", "yoshop_order_goods.*,yoshop_goods_spec.spec_value_japan,yoshop_goods_spec.goods_no");
        var goodcontent = "";
        for (var j = 0; j < goodlist.length; j++) {
            goodcontent += " 商品名称：" + goodlist[j].goods_name + " 规格：" + goodlist[j].goods_attr + " 数量：" + goodlist[j].total_num;

            var obj = {};
            obj.ordernum = orderlist[i].order_no;
            obj.name = goodlist[j].goods_name;
            obj.japanname = goodlist[j].spec_value_japan;
            obj.code = goodlist[j].goods_no;
            obj.num = goodlist[j].total_num;
            obj.price = goodlist[j].goods_price;
            obj.goods_attr = goodlist[j].goods_attr;
            obj.goods_weight = goodlist[j].goods_weight;


            if (orderlist[i].order_status == "1") {
                orderlist[i].order_status = "待付款";
            } else if (orderlist[i].order_status == "2") {
                orderlist[i].order_status = "待发货";
            } else if (orderlist[i].order_status == "3") {
                orderlist[i].order_status = "待收货";
            } else if (orderlist[i].order_status == "4") {
                orderlist[i].order_status = "已收货";
            } else if (orderlist[i].order_status == "5") {
                orderlist[i].order_status = "已评价";
            }
            obj.order_status = orderlist[i].order_status;
            obj.phone = orderlist[i].phone;


            var address = await yoshop_order_address_model.getinfo("yoshop_order_address.order_id=" + orderlist[i].id
                + " and yoshop_order_address.user_id=" + orderlist[i].user_id
                , " join yoshop_user_address on yoshop_user_address.id=yoshop_order_address.order_address_id"
                , " order by yoshop_order_address.create_time desc", "yoshop_user_address.*");
            if (address.length > 0) {
                obj.idcard = address[0].idcard;
            }
            arr.push(obj);
        }
    }


    sheet.addRows(arr);
    // function addImg() {
    //     return new Promise((resolve, reject) => {
    //         sheet.eachRow(function (row, rowNumber) {
    //             let rows = sheet.getRow(rowNumber + 1);
    //             rows.height = 80;
    //             rows.eachCell(function (cell, colNumber) {
    //                 rows.getCell(colNumber).alignment = {
    //                     vertical: "middle",
    //                     horizontal: "center",
    //                     wrapText: true,
    //                 };
    //             });
    //         });
    //         for (var i = 0; i < arr.length; i++) {
    //             if (arr[i].idcardimg1 != undefined && arr[i].idcardimg1 != "") {
    //                 var imageId = workbook.addImage({
    //                     base64: arr[i].idcardimg1,
    //                     extension: "jpeg",
    //                 });
    //                 sheet.addImage(imageId, {
    //                     tl: { col: 8.5, row: i + 1 + 0.5 },
    //                     ext: { width: 80, height: 80 },
    //                 });
    //             }
    //             if (arr[i].idcardimg2 != undefined && arr[i].idcardimg2 != "") {
    //                 var imageId = workbook.addImage({
    //                     base64: arr[i].idcardimg2,
    //                     extension: "jpeg",
    //                 });
    //                 sheet.addImage(imageId, {
    //                     tl: { col: 9.2, row: i + 1 + 0.5 },
    //                     ext: { width: 80, height: 80 },
    //                 });
    //             }




    //             if (i === arr.length - 1) {
    //                 resolve();
    //             }
    //         }
    //         resolve();
    //     });
    // }

    // addImg().then(() => {
    //     var s = "./" + fileName + ".xlsx";
    //     workbook.xlsx.writeFile(s).then(
    //         () => {
    //             res.download(s)
    //         },
    //         (err) => {
    //             res.send({
    //                 code: -1,
    //                 msg: "请联系管理员",
    //             });
    //         }
    //     );
    // });
    var s = "./" + fileName + ".xlsx";
    workbook.xlsx.writeFile(s).then(
        () => {
            res.download(s)
        },
        (err) => {
            res.send({
                code: -1,
                msg: "请联系管理员",
            });
        }
    );
});



router.get("/exportExcel2", async function (req, res) {

    var where = " yoshop_order.order_status!=0  "

    //yoshop_order.pay_status=1 and yoshop_order.order_status=2

    if (req.query.order_no != undefined && req.query.order_no != "") {
        where += " and yoshop_order.order_no like '%" + req.query.order_no + "%'";
    }
    if (req.query.phone != undefined && req.query.phone != "") {
        where += " and yoshop_user.phone like '%" + req.query.phone + "%'";
    }
    if (req.query.pay_status != undefined && req.query.pay_status != "") {
        where += " and yoshop_order.pay_status=" + req.query.pay_status;
    }

    if (req.query.stime != undefined && req.query.stime != "" && req.query.etime != undefined && req.query.etime != "") {
        where += " and yoshop_order.create_time between '" + req.query.stime + "' and '" + req.query.etime + " 23:59:59' ";
    }
    if (req.query.order_status != undefined && req.query.order_status != "") {
        where += " and yoshop_order.order_status=" + req.query.order_status;
    }




    const workbook = new Excel.Workbook();
    workbook.created = new Date();
    workbook.modified = new Date();
    let sheet = workbook.addWorksheet("excel文件名称", {
        properties: { defaultRowHeight: 80 },
    });

    sheet.columns = [
        { header: "id", key: "id", width: 15 },
        { header: "下单时间", key: "createtime", width: 15 },
        { header: "订单号", key: "ordernum", width: 15 },
        { header: "商品", key: "goodcontent", width: 15 },
        { header: "实付款", key: "total_price", width: 15 },
        { header: "用户名", key: "phone", width: 15 },
        { header: "昵称", key: "nickname", width: 15 },
        { header: "收货人姓名", key: "truename", width: 25 },
        { header: "收货人电话", key: "addressphone", width: 25 },
        { header: "收货地址", key: "address", width: 25 },
        { header: "身份证号", key: "idcard", width: 25 },
        { header: "交易状态", key: "order_status", width: 25 },
        { header: "购买方式", key: "paytype", width: 25 },
        { header: "物流费用", key: "express_price", width: 25 },
        { header: "消费积分", key: "xfscore", width: 25 },
    ];
    res.header("Access-Control-Allow-Origin", "*");

    var fileName = "order";

    console.log("where11111111111", where);
    var orderlist = await order_model.getinfo(where, " join yoshop_user on yoshop_user.user_id= yoshop_order.user_id ", " order by yoshop_order.id desc ", "yoshop_order.*,yoshop_user.nickname,yoshop_user.phone");

    var arr = [];
    for (var i = 0; i < orderlist.length; i++) {

        var goodlist = await order_goods_model.getinfo("order_id=" + orderlist[i].id);
        var goodcontent = "";
        for (var j = 0; j < goodlist.length; j++) {
            goodcontent += " 商品名称：" + goodlist[j].goods_name + " 规格：" + goodlist[j].goods_attr + " 数量：" + goodlist[j].total_num;
        }

        var obj = {};
        obj.ordernum = orderlist[i].order_no;
        obj.goodcontent = goodcontent;
        obj.total_price = orderlist[i].total_price;
        obj.createtime = moment(orderlist[i].create_time).format("YYYY-MM-DD HH:mm:ss");
        obj.a1 = "";
        obj.a2 = "";
        obj.id = orderlist[i].id;
        obj.phone = orderlist[i].phone;
        obj.nickname = orderlist[i].nickname;

        if (orderlist[i].order_status == "1") {
            orderlist[i].order_status = "待付款";
        } else if (orderlist[i].order_status == "2") {
            orderlist[i].order_status = "待发货";
        } else if (orderlist[i].order_status == "3") {
            orderlist[i].order_status = "待收货";
        } else if (orderlist[i].order_status == "4") {
            orderlist[i].order_status = "已收货";
        } else if (orderlist[i].order_status == "5") {
            orderlist[i].order_status = "已评价";
        }

        if (orderlist[i].paytype == "WXP") {
            orderlist[i].paytype = "微信";
        } else if (orderlist[i].paytype == "ALP") {
            orderlist[i].paytype = "支付宝";
        }

        obj.paytype = orderlist[i].paytype;

        obj.order_status = orderlist[i].order_status;
        obj.create_time = orderlist[i].create_time;
        obj.express_price = orderlist[i].express_price;
        obj.xfscore = orderlist[i].xfscore;



        var address = await yoshop_order_address_model.getinfo("yoshop_order_address.order_id=" + orderlist[i].id
            + " and yoshop_order_address.user_id=" + orderlist[i].user_id
            , " join yoshop_user_address on yoshop_user_address.id=yoshop_order_address.order_address_id"
            , " order by yoshop_order_address.create_time desc", "yoshop_order_address.*,yoshop_user_address.idcard");
        'select *  from  yoshop_order_address order by create_time desc where order_id=327 and user_id=32  order by id desc'

        if (address.length > 0) {

            obj.truename = address[0].name;
            obj.addressphone = address[0].phone;
            obj.address = address[0].province + address[0].city + address[0].region + address[0].detail;
            if (address[0].newaddress != null && address[0].newaddress != "") {
                obj.address = address[0].newaddress;
            }
            obj.idcard = address[0].idcard;


        }

        arr.push(obj);

    }


    sheet.addRows(arr);

    var s = "./" + fileName + ".xlsx";
    workbook.xlsx.writeFile(s).then(
        () => {
            res.download(s)
        },
        (err) => {
            res.send({
                code: -1,
                msg: "请联系管理员",
            });
        }
    );
});

function delDir(path) {
    let files = [];
    if (fs.existsSync(path)) {
        files = fs.readdirSync(path);
        files.forEach((file, index) => {
            let curPath = path + "/" + file;
            if (fs.statSync(curPath).isDirectory()) {
                delDir(curPath); //递归删除文件夹
            } else {
                fs.unlinkSync(curPath); //删除文件
            }
        });
        fs.rmdirSync(path);
    }
}


router.get("/exportimg", async function (req, res) {


    var where = " yoshop_order.order_status!=0  "

    //yoshop_order.pay_status=1 and yoshop_order.order_status=2

    if (req.query.order_no != undefined && req.query.order_no != "") {
        where += " and yoshop_order.order_no like '%" + req.query.order_no + "%'";
    }
    if (req.query.phone != undefined && req.query.phone != "") {
        where += " and yoshop_user.phone like '%" + req.query.phone + "%'";
    }
    if (req.query.pay_status != undefined && req.query.pay_status != "") {
        where += " and yoshop_order.pay_status=" + req.query.pay_status;
    }

    if (req.query.stime != undefined && req.query.stime != "" && req.query.etime != undefined && req.query.etime != "") {
        where += " and yoshop_order.create_time between '" + req.query.stime + "' and '" + req.query.etime + " 23:59:59' ";
    }
    if (req.query.order_status != undefined && req.query.order_status != "") {
        where += " and yoshop_order.order_status=" + req.query.order_status;
    }




    var orderlist = await order_model.getinfo(where, " join yoshop_user on yoshop_user.user_id= yoshop_order.user_id ", " order by yoshop_order.id desc");
    var pathurl = path.join(__dirname, '../../zip');

    delDir(pathurl);
    fs.mkdirSync(pathurl);

    var arr = [];

    var baseurl = pathurl;
    for (var i = 0; i < orderlist.length; i++) {


        var address = await yoshop_order_address_model.getinfo("order_id=" + orderlist[i].id + " and user_id=" + orderlist[i].user_id, "", " order by create_time desc");
        if (address.length > 0) {

            var user_address = await yoshop_user_address_model.getinfo("id=" + address[0].order_address_id);
            if (user_address.length > 0) {
                var truename = user_address[0].name;


                var idcardimg1 = user_address[0].idcardimg1;
                var idcardimg2 = user_address[0].idcardimg2;

                if (idcardimg1 != "" && idcardimg1 != null && idcardimg2 != null && idcardimg2 != "") {

                    var obj = {};
                    obj.truename = truename;
                    obj.idcardimg1 = idcardimg1;
                    obj.idcardimg2 = idcardimg2;

                    var result = arr.some(function (item) {
                        if (item.truename == truename) {
                            return true;
                        }
                    })
                    if (result != true) {
                        arr.push(obj);
                    }





                }

            }
        }

    }

    console.log("arr", arr.length);


    for (var i = 0; i < arr.length; i++) {

        var truename = arr[i].truename;
        var idcardimg1 = arr[i].idcardimg1;
        var idcardimg2 = arr[i].idcardimg2;

        if (fs.existsSync(baseurl + "/" + truename + "1.jpg")) {

            var result1 = await downloadFileAsync(idcardimg1, baseurl + "/" + truename + "A1.jpg");
            var result2 = await downloadFileAsync(idcardimg2, baseurl + "/" + truename + "A2.jpg");

            if (fs.existsSync(baseurl + "/" + truename + "1A.jpg")) {
                var result1 = await downloadFileAsync(idcardimg1, baseurl + "/" + truename + "B1.jpg");
                var result2 = await downloadFileAsync(idcardimg2, baseurl + "/" + truename + "B2.jpg");
            } else if (fs.existsSync(baseurl + "/" + truename + "1B.jpg")) {

                var result1 = await downloadFileAsync(idcardimg1, baseurl + "/" + truename + "C1.jpg");
                var result2 = await downloadFileAsync(idcardimg2, baseurl + "/" + truename + "C2.jpg");
            }
        }
        else {
            var result1 = await downloadFileAsync(idcardimg1, baseurl + "/" + truename + "1.jpg");
            var result2 = await downloadFileAsync(idcardimg2, baseurl + "/" + truename + "2.jpg");
        }
    }



    var newfilename = path.join(__dirname, "../../zip");
    var zip = path.join(__dirname, "../../uploads/图片.zip");
    compressing.zip.compressDir(newfilename, zip)
        .then(function () {
            var s = path.join(__dirname, '../../uploads/图片.zip');



            res.download(s)
        })
        .catch(function () {
            res.send("出错了");
        });

});

router.post("/importdata", async function (req, res) {


    console.log("ss", req.body);
    var fileurl = req.body.data;
    var now = moment().format('YYYY-MM-DD HH:mm:ss');
    var baseurl = path.resolve(__dirname, '../../');
    fileurl = baseurl + fileurl;
    console.log("fileurl", fileurl);
    var obj = xlsx.parse(fileurl);
    var excelObj = obj[0].data;


    for (var i in excelObj) {
        var arr = [];
        var value = excelObj[i];
        if (i > 0) {

            try {
                if (value[0] != undefined) {
                    var obj = {};
                    obj.ordernum = value[0];
                    obj.wuliunum = value[1];
                    var obj = await db.query("update yoshop_order set express_no=?,express_company=?,order_status=?,express_time=? where order_no=?", [obj.wuliunum, '顺丰国际', 3, now, obj.ordernum]);


                }
            } catch (ee) {
                console.log("ee", ee);
                var result = {
                    "status": "400",
                    "message": "数据格式错误",
                }
                res.jsonp(result);
                return;
            }


        }
    }

    var result = {
        "status": "200",
        "message": "success",
    }
    res.jsonp(result);



});
async function test() {
    // var newfilename = path.join(__dirname, "../../util");
    // var zip = path.join(__dirname, "../../util.zip");


    // console.log("newfilename", newfilename);
    // console.log("zip", zip);

    // compressing.zip.compressDir(newfilename, zip)
    //     .then(function () {
    //         console.log("ok");
    //     })
    //     .catch(function () {
    //         console.log("err", err);
    //     });


    // var orders = await db.query("select * from orders ", []);
    // for (var i = 0; i < orders.length; i++) {
    //     var useraddress = await db.query("select * from yoshop_user_address where id=" + orders[i].addressid, []);
    //     if (useraddress.length > 0) {
    //         var ss = await db.execute("update orders set truename=?,idcard=?,phone=?,address=?,detail=?,idcardimg1=?,idcardimg2=? where id=?", [useraddress[0].name,
    //         useraddress[0].idcard, useraddress[0].phone, useraddress[0].province + "-" + useraddress[0].city + "-" + useraddress[0].region, useraddress[0].detail, useraddress[0].idcardimg1, useraddress[0].idcardimg2, orders[i].id]);
    //         console.log("ss",ss);
    //     }

    // }

}
//test();



router.post('/orderlist', async function (req, res) {
    var page;
    var rows;
    if (isNaN(req.body.page)) {
        page = 1;
    } else {
        page = +req.body.page;
    }
    if (isNaN(req.body.rows)) {
        rows = 10;
    } else {
        rows = +req.body.rows;
    }
    var first = rows * (page - 1);


    var where = " 1=1 "
    if (req.body.order_no != undefined && req.body.order_no != "") {
        where += " and orders.order_no like '%" + req.body.order_no + "%'";
    }
    if (req.body.ordernum != undefined && req.body.ordernum != "") {
        where += " and orders.ordernum like '%" + req.body.ordernum + "%'";
    }
    if (req.body.express_no != undefined && req.body.express_no != "") {
        where += " and orders.express_no like '%" + req.body.express_no + "%'";
    }
    if (req.body.truename != undefined && req.body.truename != "") {
        where += " and yoshop_user_address.name like '%" + req.body.truename + "%'";
    }
    if (req.body.phone != undefined && req.body.phone != "") {
        where += " and yoshop_user.phone like '%" + req.body.phone + "%'";
    }
    if (req.body.state != undefined && req.body.state != "") {
        where += " and orders.state=" + req.body.state;
    }
    if (req.body.stime != undefined && req.body.stime != "" && req.body.etime != undefined && req.body.etime != "") {
        where += " and orders.createtime between '" + req.body.stime + "' and '" + req.body.etime + " 23:59:59' ";
    }



    var list = await orders_model.getlist(rows, first, where, "  join yoshop_user on yoshop_user.user_id= orders.userid ", " order by orders.id desc ",
        " orders.*,orders.phone as addressphone,yoshop_user.phone,yoshop_user.nickname,yoshop_user.avatarurl,"
        + "orders.truename as truename,orders.detail,orders.address");



    for (var i = 0; i < list.length; i++) {

        list[i].createtime = moment(list[i].createtime).format("YYYY-MM-DD HH:mm:ss");

        if (list[i].express_time != "" && list[i].express_time != null) {
            list[i].express_time = moment(list[i].express_time).format("YYYY-MM-DD HH:mm:ss");
        }



    }
    var count = await orders_model.getcount(where, "  join yoshop_user on yoshop_user.user_id= orders.userid");
    var allcount = parseInt(parseInt(count) / rows) + 1;
    if (count % rows == 0) {
        allcount = allcount - 1;
    }

    res.send({ status: 200, data: list, message: "", allcount: allcount, count: count });

});



router.post("/fahuo2", async function (req, res) {


    var obj = await db.execute("update orders set express_no=?,express_time=?,state=?,weight=? where id=?", [req.body.express_no, new Date(), 1, req.body.weight, req.body.id]);
    if (obj.code == 200) {

        var result = {
            "status": "200",
            "message": "success"
        }
        res.jsonp(result);
    } else {
        var result = {
            "status": "400",
            "message": "error"
        }
        res.jsonp(result);
    }

})
router.delete('/orders/del', async function (req, res) {

    var where = "id in (" + req.body.ids + ")";
    var obj = await orders_model.del(where);
    if (obj.code == 200) {
        var result = {
            "status": "200",
            "message": "success"
        }
        res.jsonp(result);
    } else {
        var result = {
            "status": "400",
            "message": "success"
        }
        res.jsonp(result);
    }


});


router.post("/orderedit", async function (req, res) {


    var obj = await db.execute("update orders set ordernum=? where id=?", [req.body.ordernum, req.body.id]);
    if (obj.code == 200) {
        var result = {
            "status": "200",
            "message": "success"
        }
        res.jsonp(result);
    } else {
        var result = {
            "status": "400",
            "message": "error"
        }
        res.jsonp(result);
    }

})


router.post("/importorder", async function (req, res) {


    console.log("ss", req.body);
    var fileurl = req.body.data;
    var now = moment().format('YYYY-MM-DD HH:mm:ss');
    var baseurl = path.resolve(__dirname, '../../');
    fileurl = baseurl + fileurl;
    console.log("fileurl", fileurl);
    var obj = xlsx.parse(fileurl);
    var excelObj = obj[0].data;


    for (var i in excelObj) {
        var arr = [];
        var value = excelObj[i];
        if (i > 0) {

            try {
                if (value[0] != undefined) {
                    var obj = {};
                    obj.order_no = value[0];
                    obj.express_no = value[1];
                    obj.weight = value[2];

                    var obj = await db.query("update  orders set express_no=?,express_time=?,state=?,weight=? where order_no=?", [obj.express_no, now, 1, obj.weight, obj.order_no]);

                }
            } catch (ee) {
                console.log("ee", ee);
                var result = {
                    "status": "400",
                    "message": "数据格式错误",
                }
                res.jsonp(result);
                return;
            }


        }
    }

    var result = {
        "status": "200",
        "message": "success",
    }
    res.jsonp(result);



});


router.get("/exportExcelorder", async function (req, res) {

    const workbook = new Excel.Workbook();
    workbook.created = new Date();
    workbook.modified = new Date();
    let sheet = workbook.addWorksheet("excel文件名称", {
        properties: { defaultRowHeight: 80 },
    });

    var where = "1=1";

    if (req.query.order_no != undefined && req.query.order_no != "") {
        where += " and orders.order_no like '%" + req.query.order_no + "%'";
    }
    if (req.query.ordernum != undefined && req.query.ordernum != "") {
        where += " and orders.ordernum like '%" + req.query.ordernum + "%'";
    }
    if (req.query.express_no != undefined && req.query.express_no != "") {
        where += " and orders.express_no like '%" + req.query.express_no + "%'";
    }
    if (req.query.truename != undefined && req.query.truename != "") {
        where += " and yoshop_user_address.name like '%" + req.query.truename + "%'";
    }
    if (req.query.state != undefined && req.query.state != "") {
        where += " and orders.state=" + req.query.state;
    }
    if (req.query.stime != undefined && req.query.stime != "" && req.query.etime != undefined && req.query.etime != "") {
        where += " and orders.createtime between '" + req.query.stime + "' and '" + req.query.etime + " 23:59:59' ";
    }
    if (req.query.phone != undefined && req.query.phone != "") {
        where += " and yoshop_user.phone like '%" + req.query.phone + "%'";
    }
    if (req.query.truename != undefined && req.query.truename != "") {
        where += " and yoshop_user_address.truename like '%" + req.query.truename + "%'";
    }



    sheet.columns = [
        { header: "订单号", key: "order_no", width: 15 },
        { header: "编号", key: "ordernum", width: 15 },
        { header: "备注", key: "descs", width: 15 },
        { header: "收货人姓名", key: "truename", width: 15 },
        { header: "收货人电话", key: "phone", width: 25 },
        { header: "身份证", key: "idcard", width: 25 },
        { header: "地址", key: "address", width: 25 },
        { header: "状态", key: "state", width: 25 },
        { header: "下单时间", key: "createtime", width: 25 },
        { header: "物流单号", key: "express_no", width: 25 },
        { header: "重量", key: "weight", width: 25 },
        { header: "发货时间", key: "express_time", width: 25 },
    ];
    res.header("Access-Control-Allow-Origin", "*");

    var fileName = "订单";



    var arr = [];
    var list = await orders_model.getinfo(where, " join yoshop_user_address on yoshop_user_address.id= orders.addressid join yoshop_user on yoshop_user.user_id= orders.userid ", " order by orders.id ", " orders.*,yoshop_user_address.phone,"
        + "yoshop_user_address.name as truename,yoshop_user_address.detail,yoshop_user_address.province,yoshop_user_address.city,yoshop_user_address.region,yoshop_user_address.idcard");
    for (var i = 0; i < list.length; i++) {

        list[i].address = list[i].province + list[i].city + list[i].region + list[i].detail;
        if (list[i].state == "0") {
            list[i].state = "未发货";
        } else {
            list[i].state = "已发货";
        }

        list[i].createtime = moment(list[i].createtime).format("YYYY-MM-DD HH:mm:ss");

        if (list[i].express_time != "" && list[i].express_time != null) {
            list[i].express_time = moment(list[i].express_time).format("YYYY-MM-DD HH:mm:ss");
        }




    }
    sheet.addRows(list);
    var s = "./" + fileName + ".xlsx";
    workbook.xlsx.writeFile(s).then(
        () => {
            res.download(s)
        },
        (err) => {
            res.send({
                code: -1,
                msg: "请联系管理员",
            });
        }
    );

});


router.get("/exportimgorder", async function (req, res) {

    var where = "1=1";

    if (req.query.order_no != undefined && req.query.order_no != "") {
        where += " and orders.order_no like '%" + req.query.order_no + "%'";
    }
    if (req.query.ordernum != undefined && req.query.ordernum != "") {
        where += " and orders.ordernum like '%" + req.query.ordernum + "%'";
    }
    if (req.query.express_no != undefined && req.query.express_no != "") {
        where += " and orders.express_no like '%" + req.query.express_no + "%'";
    }
    if (req.query.truename != undefined && req.query.truename != "") {
        where += " and yoshop_user_address.name like '%" + req.query.truename + "%'";
    }
    if (req.query.state != undefined && req.query.state != "") {
        where += " and orders.state=" + req.query.state;
    }
    if (req.query.stime != undefined && req.query.stime != "" && req.query.etime != undefined && req.query.etime != "") {
        where += " and orders.createtime between '" + req.query.stime + "' and '" + req.query.etime + " 23:59:59' ";
    }
    if (req.body.phone != undefined && req.body.phone != "") {
        where += " and yoshop_user.phone like '%" + req.body.phone + "%'";
    }


    var orderlist = await orders_model.getinfo(where, " join yoshop_user_address on yoshop_user_address.id= orders.addressid ", " order by orders.id ", " orders.*,yoshop_user_address.phone,"
        + "yoshop_user_address.name as truename,yoshop_user_address.detail,yoshop_user_address.province,yoshop_user_address.idcardimg2,yoshop_user_address.idcardimg1");

    var pathurl = path.join(__dirname, '../../zip');

    delDir(pathurl);
    fs.mkdirSync(pathurl);


    for (var i = 0; i < orderlist.length; i++) {

        var truename = orderlist[i].truename;


        var idcardimg1 = orderlist[i].idcardimg1;
        var idcardimg2 = orderlist[i].idcardimg2;

        var baseurl = pathurl;

        if (fs.existsSync(baseurl + "/" + truename + "1.jpg")) {

            var result1 = await downloadFileAsync(idcardimg1, baseurl + "/" + truename + "A1.jpg");
            var result2 = await downloadFileAsync(idcardimg2, baseurl + "/" + truename + "A2.jpg");

            if (fs.existsSync(baseurl + "/" + truename + "1A.jpg")) {
                var result1 = await downloadFileAsync(idcardimg1, baseurl + "/" + truename + "B1.jpg");
                var result2 = await downloadFileAsync(idcardimg2, baseurl + "/" + truename + "B2.jpg");
            } else if (fs.existsSync(baseurl + "/" + truename + "1B.jpg")) {

                var result1 = await downloadFileAsync(idcardimg1, baseurl + "/" + truename + "C1.jpg");
                var result2 = await downloadFileAsync(idcardimg2, baseurl + "/" + truename + "C2.jpg");
            }
        }
        else {
            var result1 = await downloadFileAsync(idcardimg1, baseurl + "/" + truename + "1.jpg");
            var result2 = await downloadFileAsync(idcardimg2, baseurl + "/" + truename + "2.jpg");
        }


    }

    var newfilename = path.join(__dirname, "../../zip");
    var zip = path.join(__dirname, "../../uploads/图片.zip");
    compressing.zip.compressDir(newfilename, zip)
        .then(function () {
            var s = path.join(__dirname, '../../uploads/图片.zip');
            res.download(s)
        })
        .catch(function () {
            res.send("出错了");
        });

});

router.post('/updateaddress', async function (req, res) {

    var obj = await db.execute("update yoshop_order_address set newaddress='" + req.body.address + "' where order_id=" + req.body.id);
    if (obj.code == 200) {
        var result = {
            "status": "200",
            "message": "success",
        }
        res.jsonp(result);
    } else {
        var result = {
            "status": "400",
            "message": "success"
        }
        res.jsonp(result);
    }


})
router.post('/updateprice', async function (req, res) {

    var obj = await db.execute("update yoshop_order set total_price='" + req.body.total_price + "' where id=" + req.body.id);
    if (obj.code == 200) {
        var result = {
            "status": "200",
            "message": "success",
        }
        res.jsonp(result);
    } else {
        var result = {
            "status": "400",
            "message": "success"
        }
        res.jsonp(result);
    }


})

router.post('/tj', async function (req, res) {
    var data = {
        paymoney: 0,
        paymoney_day: 0,
        paymoney_month: 0,
    }
    var where = "";
    var where2 = "";

    if (req.body.mid != undefined && req.body.mid != "" && req.body.mid != "0") {
        where += "  and mid=" + req.body.mid;
        where2 += "  and mid=" + req.body.mid;
    }


    if (req.body.stime != undefined && req.body.stime != "" && req.body.etime != undefined && req.body.etime != "") {
        where2 += " and createtime between '" + req.body.stime + "' and '" + req.body.etime + " 23:59:59' ";
    }


    var obj1 = await db.query("select sum(total_fee) as money from orders where pay_status=1 " + where2, []);
    if (obj1.length > 0) {
        data.paymoney = parseInt(obj1[0].money);
    }

    var obj2 = await db.query("select sum(total_fee) as money from orders where pay_status=1 and  to_days(createtime) = to_days(now()) " + where, []);
    if (obj2.length > 0) {
        data.paymoney_day = parseInt(obj2[0].money);
    }
    data.ordernum_day = await orders_model.getcount("pay_status=1 and  to_days(createtime) = to_days(now())");



    var obj3 = await db.query("select sum(total_fee) as money from orders where pay_status=1 and DATE_FORMAT(createtime,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') " + where, []);
    if (obj3.length > 0) {
        data.paymoney_month = parseInt(obj3[0].money);
    }

    //商品总数



    var result = {
        "status": "200",
        "message": "success",
        data: data
    }
    res.jsonp(result);


})




module.exports = router;   /*暴露这个 router模块*/